﻿--Open Symmetric Key CXUserSym
--Decryption By Certificate CXUserCert

--select top 10 * from tblCarDetailTH Order By ModifiedDate DESC
--select top 10 * from tblCarPolicy Order By ModifiedDate DESC
--select top 10 * from tblCEUsrProfile Order By ModifiedDate DESC
--select top 10 * from tblOccupationInfo Order By ModifiedDate DESC
--select top 10 * from tblNonGGCarMoreDetails Order By ModifiedDate DESC
--select top 10 * from tblNonGGTransaction Order By ModifiedDate DESC
--select top 100 * from tblPayments Order By ModifiedDate DESC

--Close Symmetric Key CXUserSym

--Open Symmetric Key CXUserSym
--Decryption By Certificate CXUserCert

Declare @tmpCustomerList Table
(
 CustomerID bigint,
 CarID bigint
)

INSERT INTO @tmpCustomerList 
Select CD.RefCEUserID, Max(CD.CarID)
FROM tblCarDetailTH CD 
INNER JOIN tblCarPolicy CP ON CD.CarID = CP.CECarID And CD.RefCEUserID = CP.RefCEUserID
INNER JOIN tblCEUsrProfile UP ON CD.RefCEUserID = UP.RefCEUserID
INNER JOIN tblOccupationInfo O ON UP.Occupation = O.OccupationID
INNER JOIN tblCEUser U ON CP.RefCEUserID = U.CEID
LEFT JOIN tblCXCRMCallStatus CL ON U.CEID = CL.CustomerId
LEFT JOIN tblPayments P ON CP.CECArID = P.CECarID And CP.RefCEUserID = P.RefCEUserID
WHERE CD.RefCEUserID > 0 And CD.ModifiedDate Between '20120101' And '20120505'
AND (CL.CallStatusTypeID IS NULL OR CL.CallStatusTypeID <> 2)
AND U.UsrTypeId = 400 AND (U.IsReject IS NULL OR U.IsReject = 0)
AND (CD.Info7 NOT LIKE '%[A-Z,a-z]%' AND CD.Info8 NOT LIKE '%[A-Z,a-z]%')
Group By CD.RefCEUserID

--Select * from @tmpCustomerList

Select Convert(varchar(25), UP.DOB, 103) AS YEAR_BIRTH,
UP.Gender AS GENDER,
CD.CarID AS UNIQUE_ID,
0 AS acctno1,
'' AS TIER_INCOME,
CP.ProvinceRegistration AS AREA,
'' AS [DESCRIPTION],
CD.Info5 AS NOTE1,
O.TH AS NOTE2,
('(ปีของรถยนต์ (ค.ศ.) = ' + CAST(CD.ManufactYear AS VARCHAR) + ') ' + CD.MakeModelDesc) AS NOTE3,

('ประเภทประกัน = ' + CASE CP.InsuranceType 
						WHEN 11 THEN 'Class 1 (Low Cost)'
						WHEN 10 THEN 'Class 1'
						WHEN 21 THEN 'Class 2+'
						WHEN 31 THEN 'Class 3+'
						WHEN 30 THEN 'Class 3' END +
', ประเภทอู่ = ' + CASE CP.GarageType 
						WHEN 1 THEN 'อู่ประกัน'
						WHEN 2 THEN 'อู่ห้าง' END +
					
', ส่วนลดประวัติดี = ' + CAST(CP.NCDPercent AS VARCHAR) + '%, จำนวนผู้ขับขี่ = ' + CAST(CP.NoOfDriver AS VARCHAR) + ', ผู้รับประกันภัยปัจจุบัน = ' + CP.CurrentInsurer) 

AS NOTE4,
CASE 
WHEN P.PaymentType IS NULL THEN 'N/A'
WHEN P.PaymentType = 'CCIP' OR P.PaymentType = 'CCOL' THEN 'Y'
ELSE 'N'
END AS NOTE5,
'' AS title,
CD.Info8 AS fname,
CD.Info7 AS lname,
'' AS etitle,
'' AS efname,
'' AS elname,
'' AS H_areacd1,
'' AS H_phnnum1,
'' AS H_phnext1,
'' AS B_areacd1,
'' AS B_phnnum1,
'' AS B_phnext1,
'' AS M_areacd1,
CD.Info6 AS M_phnnum1,
'' AS M_phnext1,
CASE
WHEN P.ModifiedDate IS NULL THEN 'N/A'
ELSE Convert(varchar(25), P.ModifiedDate, 103)
END AS [Purchase Date]
FROM tblCarDetailTH CD 
INNER JOIN tblCarPolicy CP ON CD.CarID = CP.CECarID And CD.RefCEUserID = CP.RefCEUserID
INNER JOIN @tmpCustomerList TMP ON CD.CarID = TMP.CarID And CD.RefCEUserID = TMP.CustomerID
INNER JOIN tblCEUsrProfile UP ON CD.RefCEUserID = UP.RefCEUserID
INNER JOIN tblOccupationInfo O ON UP.Occupation = O.OccupationID
LEFT JOIN tblPayments P ON CP.CECArID = P.CECarID And CP.RefCEUserID = P.RefCEUserID
WHERE CD.RefCEUserID > 0
ORDER BY CD.ModifiedDate DESC
